#!/usr/bin/env python
# -*- coding: utf-8 -*-
from string import Template
from tack.lib.configfile import ConfigFile
from tack.lib.modtools import load_config
from tack.lib import string_app, json_app, content_headers, status_codes
from tack_ocie.api.util import build_row_dict, sql_string, sql_unstring, sql_int


insert_unit = """
INSERT INTO ocie_unit (description, active)
VALUES ($description, $active)
"""

get_all_units = """
SELECT * 
FROM ocie_unit
ORDER BY description
"""

get_unit_by_id = """
SELECT * 
FROM ocie_unit
WHERE unit_id = $unit_id
"""

get_unit_by_description = """
SELECT *
FROM ocie_unit
WHERE description = $description
"""

get_unit_check = """
SELECT *
FROM ocie_unit
WHERE description = $description AND
    unit_id <> $unit_id
"""

update_unit_by_id = """
UPDATE ocie_unit
SET description = $description,
    active = $active
WHERE unit_id = $unit_id
"""

get_all_email = """
SELECT keyword, subject, markup
FROM message_template
WHERE ( keyword = 'ocie_appt_conf' OR
    keyword = 'ocie_appt_rem' OR
    keyword = 'ocie_not_canc' )
"""

update_email = """
UPDATE message_template
SET subject = $subject,
    markup = $markup
WHERE keyword = $keyword
"""


def get_public(environ, start_response):
    cfg = environ['tack.siteconfig']
    output = {
        'ocie_poc_name':cfg['ocie_poc_name'],
        'ocie_poc_email':cfg['ocie_poc_email'],
        'ocie_poc_phone':cfg['ocie_poc_phone'],
        'ocie_pr_office_name':cfg['ocie_pr_office_name'],
        'ocie_pr_phone':cfg['ocie_pr_phone']
        }
    return json_app(output)(environ, start_response)


def get_site(environ, start_response):
    cfg = environ['tack.siteconfig']
    output = {
        'ocie_poc_name':cfg['ocie_poc_name'],
        'ocie_poc_email':cfg['ocie_poc_email'],
        'ocie_poc_phone':cfg['ocie_poc_phone'],
        'ocie_pr_office_name':cfg['ocie_pr_office_name'],
        'ocie_pr_email':cfg['ocie_pr_email'],
        'ocie_pr_phone':cfg['ocie_pr_phone']
        }
    return json_app(output)(environ, start_response)


def post_site(environ, start_response):
    form = environ['form']
    cfg = load_config(environ)
    fields = ['ocie_poc_name', 'ocie_poc_email', 'ocie_poc_phone',
              'ocie_pr_office_name', 'ocie_pr_email', 'ocie_pr_phone']
    for k in fields:
        if k in form:
            cfg.set(k,form[k])
    cfg.save(cfg.filename)
    return string_app('Updated')(environ, start_response)


def get_units(environ, start_response):
    sql = get_all_units
    cursor = environ['connection'].cursor()
    cursor.execute(sql)
    rows = cursor.fetchall()
    output = []
    for row in rows:
        row_dict = build_row_dict(cursor.description, row)
        output.append(row_dict)
    return json_app(output)(environ, start_response)


def add_unit(environ, start_response):
    description = sql_string(environ['form']['description'])
    active = sql_int(environ['form']['active'])
    sql = Template(get_unit_by_description).substitute(locals())
    cursor = environ['connection'].cursor()
    cursor.execute(sql)
    rows = cursor.fetchall()
    if len(rows):
        start_response(status_codes.conflict, content_headers.default_plain())
        return ['Unit Exists']
    sql = Template(insert_unit).substitute(locals())
    cursor.execute(sql)
    environ['connection'].commit()
    cursor.execute('SELECT CAST(@@IDENTITY AS INT)')
    rows = cursor.fetchall()
    output = rows[0]
    return json_app(output)(environ, start_response)


def get_unit(environ, start_response):
    unit_id = environ['dispatcher.groups']['unit_id']
    sql = Template(get_unit_by_id).substitute(locals())
    cursor = environ['connection'].cursor()
    cursor.execute(sql)
    rows = cursor.fetchall()
    if rows:
        output = build_row_dict(cursor.description, rows[0])
    else:
        start_response(status_codes.not_found, content_headers.default_plain())
        return ['404 Not Found']
    return json_app(output)(environ, start_response)
    

def update_unit(environ, start_response):
    unit_id = environ['dispatcher.groups']['unit_id']
    description = sql_string(environ['form']['description'])
    active = sql_int(environ['form']['active'])
    sql = Template(get_unit_check).substitute(locals())
    cursor = environ['connection'].cursor()
    cursor.execute(sql)
    rows = cursor.fetchall()
    if len(rows):
        start_response(status_codes.conflict, content_headers.default_plain())
        return ['Unit Exists']
    sql = Template(update_unit_by_id).substitute(locals())
    cursor = environ['connection'].cursor()
    cursor.execute(sql)
    environ['connection'].commit()
    output = 'Updated'
    return json_app(output)(environ, start_response)


def get_email(environ, start_response):
    sql = get_all_email
    cursor = environ['connection'].cursor()
    cursor.execute(sql)
    rows = cursor.fetchall()
    output = {}
    for row in rows:
        k = row.keyword + '_subject_line'
        output[k] = sql_unstring(row.subject)
        k = row.keyword + '_text'
        output[k] = sql_unstring(row.markup)
    return json_app(output)(environ, start_response)


def post_email(environ, start_response):
    form = environ['form']
    cursor = environ['connection'].cursor()
    allowed = ['ocie_appt_conf', 'ocie_appt_rem', 'ocie_not_canc']
    if form['keyword'] in allowed:
        keyword = sql_string(form['keyword'])
        subject = sql_string(form['subject'])
        markup = sql_string(form['markup'])
        sql = Template(update_email).substitute(locals())
        cursor.execute(sql)
        environ['connection'].commit()
    return string_app('Updated')(environ, start_response)
    
    